package connectors;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import entities.Notifica;
import entities.Verbale;

public class DBManager {
	
	
	static Logger logger = Logger.getLogger(DBManager.class);
	private static DataSource dataSource;

	private static DBManager dbManager;
	

	public static synchronized DBManager getDBManager(){
		if (dbManager == null) {
			dbManager = new DBManager();
		}
		return dbManager;
	}
		
	public Object clone() throws CloneNotSupportedException {
		throw new CloneNotSupportedException();
	}
	
	
	private DBManager(){
		Context initContext;
		try {
			initContext = new InitialContext();
			Context envContext  = (Context)initContext.lookup("java:/comp/env");
	        dataSource = (DataSource)envContext.lookup("jdbc/gmm");
	        
		} catch (NamingException e) {
			logger.error("Errore nella gestione del pool: "+e.getMessage());
		}      
	}
	

	public Connection connect(){
		Connection con = null;
	    try {	  
			con = dataSource.getConnection();
		} catch (Exception e) {
			logger.error("Errore nella connessione al DB: "+e.getMessage());
		}
	    return con;	    	
	}
	
	public void disconnect(Connection con){
			if(con==null)return;
	        try {
				con.close();
			} catch (SQLException e) {
				logger.error("Errore nella disconnessione dal DB: "+e.getMessage());
			}	    
	}
	


	public String storeNotifica(Connection con,Notifica notifica,String matricola,String codice_agente,String IMEI) throws SQLException{
		String esito = "";
		CallableStatement stmt = con.prepareCall("{call inserisciNotifica (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
		stmt.setString(1, notifica.getVerbaleNumber());
		stmt.setString(2, notifica.getDataOra());
		stmt.setString(3, notifica.getViaComune());
		stmt.setDouble(4, notifica.getSanzione());
		stmt.setString(5, notifica.getTarga());
		stmt.setString(6, notifica.getInfrazioni().toString());
		stmt.setString(7, notifica.getDescrizioneInfrazione().toString());
		stmt.setString(8, matricola);
		stmt.setBlob(9, notifica.getFoto());		
		stmt.setString(10, codice_agente);
		stmt.setString(11, IMEI);
		stmt.setDouble(12, notifica.getLat());
		stmt.setDouble(13, notifica.getLongi());
        ResultSet rs = stmt.executeQuery();
        if(rs!=null && rs.next()){
        		esito = rs.getString("Esito");
        }
        if(esito.equals("Successo")) logger.info("nuova notifica "+notifica.getVerbaleNumber()+" inserita nel DB");
        return esito;       	
	}
	

	public String deleteNotifica(Connection con,String numero_verbale,String matricola,String codice_agente,String IMEI) throws SQLException{
		String esito = "";
		CallableStatement stmt = con.prepareCall("{call cancellaNotifica (?, ?, ?, ?)}");
		stmt.setString(1, numero_verbale);
		stmt.setString(2, matricola);		
		stmt.setString(3, codice_agente);
		stmt.setString(4, IMEI);
        ResultSet rs = stmt.executeQuery();
        if(rs!=null && rs.next()){

        		esito = rs.getString("Esito");
        }
        if(esito.equals("Successo")) logger.info("cancellata notifica "+numero_verbale+" dal DB");
        return esito;
	}
	

	public String login(Connection con,String matricola,String codice_agente,String IMEI) throws SQLException{
		String esito = "";
		CallableStatement stmt = con.prepareCall("{call login (?, ?, ?)}");
		stmt.setString(1, matricola);		
		stmt.setString(2, codice_agente);
		stmt.setString(3, IMEI);
        ResultSet rs = stmt.executeQuery();
        if(rs!=null && rs.next()){
        		esito = rs.getString("Esito");
        }
        if(esito.equals("Successo")) logger.info("matricola "+matricola+" autenticata");
        return esito;
	}
	
	
	
	public String storeVerbale(Connection con,Verbale verbale,String matricola,String codice_agente,String IMEI) throws SQLException{

		String esito = "";
        CallableStatement stmt = con.prepareCall("{call inserisciVerbale (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
        										" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
		stmt.setString(1,  verbale.getNumeroVerbale());
		stmt.setString(2,  verbale.getData_ora());
		stmt.setString(3,  verbale.getLuogoTrasgressione());
		stmt.setString(4,  verbale.getSottoscritti());
		stmt.setString(5,  verbale.getNome_cognome_trasgressore());
		stmt.setString(6,  verbale.getNascitaTrasgressore());
		stmt.setString(7,  verbale.getProvNascitaTrasgr());
		stmt.setString(8,  verbale.getDataNascitaTrasgr());
		stmt.setString(9,  verbale.getResidenzaTrasgr());
		stmt.setString(10, verbale.getProvResidenzaTrasgr());
		stmt.setString(11, verbale.getDocumento());
		stmt.setString(12, verbale.getnDocTrasgr());
		stmt.setString(13, verbale.getDocumentoRilasciatoData());
		stmt.setString(14, verbale.getDocumentoRilasciatoDa());
		stmt.setString(15, verbale.getVeicolo());
		stmt.setString(16, verbale.getTarga());
		stmt.setString(17, verbale.getNome_cognome_possessore());
		stmt.setString(18, verbale.getNascitaPossessore());
		stmt.setString(19, verbale.getProvNascitaPoss());
		stmt.setString(20, verbale.getDataNascitaPoss());
		stmt.setString(21, verbale.getResidenzaPoss());
		stmt.setString(22, verbale.getProvResidenzaPoss());
		stmt.setString(23, verbale.getViolazioni());
		stmt.setString(24, verbale.getDichiara());
		stmt.setDouble(25, verbale.getSanzione());
		stmt.setString(26, matricola);
		stmt.setBlob(27, verbale.getFoto());		
		stmt.setString(28, codice_agente);
		stmt.setString(29, IMEI);
		stmt.setDouble(30, verbale.getLat());
		stmt.setDouble(31, verbale.getLongi());
        
        ResultSet rs = stmt.executeQuery();
        if(rs!=null && rs.next()){

        		esito = rs.getString("Esito");
        }
        if(esito.equals("Successo")) logger.info("nuovo verbale "+ verbale.getNumeroVerbale()+" inserito nel DB");
        return esito;
				
	}
	
	


}
